package cn.com.shopec.erp.product.commo;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.List;

import javax.servlet.ServletOutputStream;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import cn.com.shopec.erp.common.utils.StringUtil;
import cn.com.shopec.erp.product.model.ProdChangePrice;

public class ChangPriceExcelExport {

    /** 日志. */
    private static Logger log = Logger.getLogger(ChangPriceExcelExport.class);
    /** 工作簿. */
    private HSSFWorkbook workbook = null;
    /** 工作表. */
    private HSSFSheet sheet = null;
    /** 工作行. */
    private HSSFRow row = null;
    /** 单元 格. */
    private HSSFCell cell = null;
    /** 最大SHEET数量. */
    private static final int SHEET_MAX_COUNT = 65535;

    /**
     * 
     * @MethodName createFirstRow
     * @Description 设置第一行
     * @author duhui
     * @date 2013-7-12 上午9:30:52
     */
    private void createFirstRow() {
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 22);
        this.sheet.addMergedRegion(cra);
        this.row = this.sheet.createRow(0);
        this.cell = this.row.createCell(0);
        HSSFCellStyle style = this.workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont font = this.workbook.createFont();
        font.setFontHeight(HSSFFont.BOLDWEIGHT_NORMAL);
        style.setFont(font);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("深圳报业集团电子商务有限公司");
    }

    /**
     * 
     * @MethodName createSecondRow
     * @Description 设置第二行
     * @author duhui
     * @date 2013-7-12 上午9:30:35
     */
    private void createSecondRow() {
        CellRangeAddress cra = new CellRangeAddress(1, 1, 0, 22);
        this.sheet.addMergedRegion(cra);
        this.row = this.sheet.createRow(1);
        this.cell = this.row.createCell(0);
        HSSFCellStyle style = this.workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont font = this.workbook.createFont();
        font.setFontHeight((short) 250);
        style.setFont(font);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("B2C商品变价单");
    }

    /**
     * 
     * @MethodName createThirdRow
     * @Description 设置第三行
     * @author duhui
     * @date 2013-7-12 上午9:30:21
     */
    private void createThirdRow() {
        CellRangeAddress cra = new CellRangeAddress(2, 2, 0, 22);
        this.sheet.addMergedRegion(cra);
        this.row = this.sheet.createRow(2);
        this.cell = this.row.createCell(0);
        HSSFCellStyle style = this.workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("文件编码:________");
    }

    /**
     * 
     * @MethodName createFourthRow
     * @Description 设置第四行
     * @author duhui
     * @date 2013-7-12 上午9:30:05
     */
    private void createFourthRow() {
        HSSFCellStyle style = this.workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        this.row = this.sheet.createRow(3);
        this.row.setHeight((short) 400);

        this.cell = this.row.createCell(0);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("采购部门");

        CellRangeAddress cra1 = new CellRangeAddress(3, 3, 1, 2);
        this.sheet.addMergedRegion(cra1);

        this.cell = this.row.createCell(3);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("申请日期");

        CellRangeAddress cra2 = new CellRangeAddress(3, 3, 4, 5);
        this.sheet.addMergedRegion(cra2);

        this.cell = this.row.createCell(6);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("申请人");

        CellRangeAddress cra3 = new CellRangeAddress(3, 3, 7, 8);
        this.sheet.addMergedRegion(cra3);

        this.cell = this.row.createCell(9);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("申请人联系电话");

        CellRangeAddress cra4 = new CellRangeAddress(3, 3, 10, 11);
        this.sheet.addMergedRegion(cra4);

        this.cell = this.row.createCell(12);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("交货地点");

        CellRangeAddress cra5 = new CellRangeAddress(3, 3, 13, 14);
        this.sheet.addMergedRegion(cra5);

        this.cell = this.row.createCell(15);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("仓库联系方式");

        CellRangeAddress cra6 = new CellRangeAddress(3, 3, 16, 17);
        this.sheet.addMergedRegion(cra6);
    }

    /**
     * 
     * @MethodName createTitle
     * @Description 设置标题
     * @author duhui
     * @date 2013-7-12 上午9:29:19
     */
    private void createTitle() {
        HSSFCellStyle style = this.workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        this.row = this.sheet.createRow(4);
        this.row.setHeight((short) 400);

        String[] title = new String[] { "序号", "商品条码", "商品编码", "商品分类", "商品品牌", "商品名称", "商品规格", "正常含税成本","正常未税成本", "正常含税零售","正常未税零售",
                "正常毛利率(%)", "促销含税成本","促销未税成本", "促销含税零售价","促销未税零售价", "促销毛利率(%)", "税率(%)", "库存数量", "促销变价开始时间", "促销变价结束时间", "供应商编号", "供应商名称" };

        for (int i = 0; i < title.length; i++) {
            this.cell = this.row.createCell(i);
            this.cell.setCellStyle(style);
            this.cell.setCellValue(title[i]);
        }
    }

    /**
     * 
     * @MethodName setRowValue
     * @Description 设置行参数
     * @author duhui
     * @date 2013-7-12 上午9:28:47
     * @param prodChangePrice
     * @param j
     */
    private void setRowValue(ProdChangePrice prodChangePrice, int j) {
        if (null != prodChangePrice) {
        	int i = 0;
        	Double price = 0.00;
        	DecimalFormat df = new DecimalFormat("#.00");
        	Double prodRate = 0.00;
        	if(StringUtil.isExist(prodChangePrice.getProdRate())){
        		prodRate = Double.valueOf(prodChangePrice.getProdRate())/100;
        	}
        	
            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(String.valueOf(j + 1));

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getProdBarCode());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getProdNo());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getCatName());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getBrandName());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getCommoName());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getSpecName());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getOriginalCost());
            
            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getUnOriginalCost());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getOriginalRetailCost());
            
            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getUnOriginalRetailCost());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getOriginalRate());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getPromotionsCost());
            
            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getUnPromotionsCost());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getPromotionsRetailCost());
            
            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getUnPromotionsRetailCost());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getPromotionsRate());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getProdRate());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getProdInventory());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getValidTimeStart());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getValidTimeEnd());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getSupplierNo());

            this.cell = this.row.createCell(i++);
            this.cell.setCellValue(prodChangePrice.getSupplierName());

        }
    }

    /**
     * 
     * @MethodName createLastRow
     * @Description 创建最后一行表格
     * @author duhui
     * @date 2013-7-12 上午9:28:25
     * @param poeVos
     */
    private void createLastRow(List<ProdChangePrice> prodChangePrices) {
        int lastRow = 0;
        if (null != prodChangePrices && prodChangePrices.size() > 20) {
            lastRow = prodChangePrices.size() + 5;
        } else {
            lastRow = 25;
        }
        HSSFCellStyle style = this.workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        CellRangeAddress cra1 = new CellRangeAddress(lastRow, lastRow + 2, 0, 0);
        this.sheet.addMergedRegion(cra1);
        this.row = this.sheet.createRow(lastRow);
        this.cell = this.row.createCell(0);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("采购部");

        this.cell = this.row.createCell(1);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("经办");

        CellRangeAddress cra2 = new CellRangeAddress(lastRow, lastRow, 2, 3);
        this.sheet.addMergedRegion(cra2);

        CellRangeAddress cra3 = new CellRangeAddress(lastRow + 1, lastRow + 1, 2, 3);
        this.sheet.addMergedRegion(cra3);

        CellRangeAddress cra4 = new CellRangeAddress(lastRow + 2, lastRow + 2, 2, 3);
        this.sheet.addMergedRegion(cra4);

        CellRangeAddress cra5 = new CellRangeAddress(lastRow, lastRow + 2, 4, 4);
        this.sheet.addMergedRegion(cra5);
        this.cell = this.row.createCell(4);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("总经理批准");

        CellRangeAddress cra6 = new CellRangeAddress(lastRow, lastRow + 2, 5, 6);
        this.sheet.addMergedRegion(cra6);

        CellRangeAddress cra7 = new CellRangeAddress(lastRow, lastRow + 2, 7, 7);
        this.sheet.addMergedRegion(cra7);
        this.cell = this.row.createCell(7);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("董事长批准");

        CellRangeAddress cra8 = new CellRangeAddress(lastRow, lastRow + 2, 8, 9);
        this.sheet.addMergedRegion(cra8);

        CellRangeAddress cra9 = new CellRangeAddress(lastRow, lastRow + 2, 10, 10);
        this.sheet.addMergedRegion(cra9);
        this.cell = this.row.createCell(10);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("账务批准");

        CellRangeAddress cra10 = new CellRangeAddress(lastRow, lastRow + 2, 11, 12);
        this.sheet.addMergedRegion(cra10);

        CellRangeAddress cra11 = new CellRangeAddress(lastRow, lastRow + 2, 13, 13);
        this.sheet.addMergedRegion(cra11);
        this.cell = this.row.createCell(13);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("合计");

        this.cell = this.row.createCell(14);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("税前金额");

        CellRangeAddress cra12 = new CellRangeAddress(lastRow, lastRow, 15, 16);
        this.sheet.addMergedRegion(cra12);

        CellRangeAddress cra13 = new CellRangeAddress(lastRow + 1, lastRow + 1, 15, 16);
        this.sheet.addMergedRegion(cra13);

        CellRangeAddress cra14 = new CellRangeAddress(lastRow + 2, lastRow + 2, 15, 16);
        this.sheet.addMergedRegion(cra14);

        CellRangeAddress cra15 = new CellRangeAddress(lastRow, lastRow + 2, 17, 17);
        this.sheet.addMergedRegion(cra15);
        this.cell = this.row.createCell(17);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("备注");

        CellRangeAddress cra16 = new CellRangeAddress(lastRow, lastRow + 2, 18, 22);
        this.sheet.addMergedRegion(cra16);

        this.row = this.sheet.createRow(lastRow + 1);
        this.cell = this.row.createCell(1);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("经理");

        this.cell = this.row.createCell(14);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("税额");

        this.row = this.sheet.createRow(lastRow + 2);
        this.cell = this.row.createCell(1);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("总监");

        this.cell = this.row.createCell(14);
        this.cell.setCellStyle(style);
        this.cell.setCellValue("税后金额");
    }

    /**
     * 
     * @MethodName createWrite
     * @Description 输出表格
     * @author chengui
     * @date 2013-7-12 上午9:27:32
     * @param out
     */
    public void createWrite(OutputStream out) {
        try {
            this.workbook.write(out);
        } catch (FileNotFoundException e) {
            log.error("DataExport createWrite FileNotFoundException:", e);
        } catch (IOException e) {
            log.error("DataExport createWrite IOException:", e);
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    log.error("DataExport createWrite IOException:", e);
                }
            }
        }
    }

    /**
     * 
     * @MethodName createSheet
     * @Description 创建表格
     * @author chengui
     * @date 2013-7-12 上午9:27:58
     * @param poeVos
     * @param outputStream
     */
    public void createSheet(List<ProdChangePrice> prodChangePrices, ServletOutputStream outputStream) {
        // 默认为1
        int sheetNum = 1;
        int totalSize = prodChangePrices.size() + 20;

        // 计算要创建的sheet个数
        if (totalSize % SHEET_MAX_COUNT > 0) {
            sheetNum = prodChangePrices.size() / SHEET_MAX_COUNT + 1;
        } else {
            sheetNum = prodChangePrices.size() / SHEET_MAX_COUNT;
        }

        this.workbook = new HSSFWorkbook();
        /** 循环工作表 **/
        for (int i = 0; i < sheetNum; i++) {
            // i ----------------工作表数量
            this.sheet = this.workbook.createSheet(("sheet" + i));

            this.createFirstRow();
            this.createSecondRow();
            this.createThirdRow();
            this.createFourthRow();
            this.createTitle();

            /** 循环工作表的第一行 **/
            for (int j = i * SHEET_MAX_COUNT; j < (i + 1) * SHEET_MAX_COUNT; j++) {
                if (j > totalSize) {
                    break;
                } else {
                    /** 初始化行对象 **/
                    if (j < prodChangePrices.size()) {
                        this.row = this.sheet.createRow(j + 5 - (i * SHEET_MAX_COUNT));
                        this.setRowValue(prodChangePrices.get(j), j);
                    } else {
                        break;
                    }
                }
            }

            this.createLastRow(prodChangePrices);
        }

        this.createWrite(outputStream);
    }

}
